#!python
# -*- coding: utf-8 -*-

from flask import Blueprint, request, url_for, \
        render_template, send_from_directory,\
        current_app, Response, stream_with_context
import json
import pymysql
from werkzeug import secure_filename
import os
import time
import urlparse
import codecs
import traceback
import unicodecsv
from string import Template
from .admin import login_required
from .db import get_db

data_import = Blueprint("data_import", __name__)

display_p=u"""<!DOCTYPE html>
<html><head>
    <title>数据导入结果</title>
    <style type="text/css">
        body{font-size:13px;} 
        table,td,td,tr,th{
            border: 1px solid #ccc; 
            border-collapse: collapse;
        }        
        th{ text-align: left;} 
        table{width: 100%}
    </style>
    </head>
<body>
    <div> 数据导入到 ${table} 的如下列:<br> &nbsp;&nbsp;${cols}<br>
    </div>
    <div><table>"""

@data_import.route("/import", methods=["GET", "POST"])
@login_required
def batch_add():
    if request.method == "GET":
        return render_template("batch_add.html")
    f = request.files["data"] 
    if not f:
        return "empty file"
    if not f.filename.endswith(".csv"):
        return "unsupported file type"
    fn = secure_filename(f.filename)
    fn_f = os.path.join(
            current_app.config["UPLOAD_FOLDER"],
            fn)
    ua=request.headers.get("user-agent")
    if "windows" in ua.lower():
        coding = "gbk"
    else:
        coding = "utf-8"
    f.save(fn_f)
    def generate():
        fp = open(fn_f)
        r = unicodecsv.reader(fp, encoding = coding)
        conn = get_db()
        cur = conn.cursor()
        try:
            l = r.next()
            table = l[0]
            if table[0] == u'\ufffe' or table[0] == u'\ufeff':
                table = table[1:]
            columns = r.next()
            sql = u"select %s from %s where 1 limit 1" % (",".join(columns), table)
            try:
                cur.execute(sql)
                cur.fetchall()
            except:
                traceback.print_exc()
                yield '数据导入失败:<font color="red">列名有误, 请检查!</font>'
                raise StopIteration()
            s=Template(display_p).safe_substitute (
                    table = table,
                    cols = ",".join(columns)
                    )
            yield s.encode("utf-8")
            s = u"<tr>"
            for i in columns:
                s += u"<th>%s</th>" % i
            s += u"<th></th></tr>"
            yield s.encode("utf-8")
            for l in r:
                values = l
                if not values[0]:
                    continue
                sql = u"select {col0} from {table} where {col0} = '{val0}'".format(
                        col0 = columns[0],
                        val0 = values[0],
                        table = table
                        )
                current_app.logger.debug(sql)
                cur.execute(sql)
                rs = cur.fetchall()
                if rs:
                    ws = []
                    for i in range(1,len(values)):
                        w1 = u"%s='%s'" % (columns[i], pymysql.escape_string(values[i]))
                        ws.append(w1)
                    sql = u"update {table} set {w} where {col0} = '{val0}'".format(
                            table=table,
                            col0=columns[0],
                            val0=values[0],
                            w = ",".join(ws)
                            )
                    current_app.logger.debug(sql)
                    cur.execute(sql)
                else:
                    sql = u"insert into %s (%s) values(%s)" % (
                            table,
                            ",".join(columns),
                            ",".join(["'%s'" % (pymysql.escape_string(s.strip()),) for s in values])
                            )
                    current_app.logger.debug(sql)
                    cur.execute(sql)
                s = u"<tr>"
                for i in values:
                    s += u"<td>%s</td>" % i
                s += u'<td><font color="blue">导入成功!</font></td></tr>'
                yield s.encode("utf-8")
            s=u"</table></div></body></html>"
            yield s.encode("utf-8")
        finally:
            try:
                conn.commit()
            except:
                pass
            cur.close()
            fp.close()
            os.unlink(fn_f)
    r = Response(stream_with_context(generate()))
    r.headers["Content-Type"] = "text/html; charset=utf-8"
    return r
